﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using iWMS.DAL;
using System.Data.SqlClient;
using vbReturn.classes;
using System.Data.OleDb;
using System.IO;

namespace vbReturn
{
    public partial class frmUploadRGA : Form
    {
       
        //------------
        private FolderBrowserDialog folderBrowserDialog1;
        private string folderName;
        
        public frmUploadRGA()
        {
            InitializeComponent();
            initPage();
        }

        private void initPage()
        {
           clearPage();
           
        }

        private void clearPage()
        {
            //------------
            this.lblShortage.Visible = false;
            this.dgExternOrders.Visible = false;

            //------------
            this.lblExcess.Visible = false;
            this.dgExcess.Visible = false;

            //------------
            this.lblAlien.Visible = false;
            this.dgAlien.Visible = false;

            //------------
            this.lblRGADetails.Visible = false;
            this.dgRGADetails.Visible = false;

            //------
            btnUploadRGA.Visible = false;

            //-------
            this.folderBrowserDialog1 = new System.Windows.Forms.FolderBrowserDialog();
            this.folderBrowserDialog1.Description =
            "Select the directory that you want to use as the default.";

            //----------
            chkChooseDir.Visible = false; //disable for now
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            string sErrorMsg = null;
            
            if (string.IsNullOrEmpty(txtReceiptKeyNo.Text.Trim()) == true)
            {
                MessageBox.Show("Please enter Extern Order No!!!");
                return;
            }

            if (!checkIfReturnIDValid(txtReceiptKeyNo.Text.Trim()))
            {
                MessageBox.Show("No such Record!!!");
                return;
            }

            sErrorMsg = GetShortageInfo(clsConfig.SMSSQLConnectionString);
            sErrorMsg = GetExcessInfo(clsConfig.SMSSQLConnectionString);
            sErrorMsg = GetRGAPickDetail(clsConfig.SMSSQLConnectionString);
            GetAlienInfo(clsConfig.SMSSQLConnectionString);
            btnUploadRGA.Visible = true;
            
        }

        //------------------------------------
        public string GetShortageInfo(string conn)
        {
            string sErrorMsg = null;

            string sQuery = "SELECT ReceiptKey, ExternReceiptKey, SKU, isnull(sum(QtyExpected),0) as TotalQtyExpected, " +
                            "isnull(sum(QtyScan) +sum(QtyScanDamage) + sum(QtyScanExcess) ,0) as TotalScanQty, " +
                            "isnull(sum(QtyExpected),0) - isnull(sum(QtyScan) +sum(QtyScanDamage) + sum(QtyScanExcess) ,0) as ShortageQty " +
                            "FROM packaus.dbo.RGA_Det WHERE ReturnID = '" + txtReceiptKeyNo.Text.Trim() + "' " +
                            "Group by ReceiptKey, ExternReceiptKey, SKU " +
                            "HAVING isnull(sum(QtyExpected),0) - isnull(sum(QtyScan) +sum(QtyScanDamage) + sum(QtyScanExcess) ,0) > 0 ";

            sErrorMsg = bindDataToDataGrid(sQuery, conn, ref this.dgExternOrders, ref this.lblShortage);

            if (!string.IsNullOrEmpty(sErrorMsg))
            {
                MessageBox.Show(sErrorMsg);
            }

            return sErrorMsg;
        }

        public string GetRGAPickDetail(string conn)
        {
            string sErrorMsg = null;

            string sQuery = "select ExternLineNo, sku, QtyExpected, uom, loc, Condition, QtyScan, QtyScanDamage from packaus.dbo.RGA_Det where ReturnID = '" + txtReceiptKeyNo.Text.Trim() + "' and storerkey = '" + clsConfig.SStorer + "'";

            sErrorMsg = bindDataToDataGrid(sQuery, conn, ref this.dgRGADetails, ref this.lblRGADetails);

            if (!string.IsNullOrEmpty(sErrorMsg))
            {
                MessageBox.Show(sErrorMsg);
            }

            return sErrorMsg;
        }

        public string GetExcessInfo(string conn)
        {
            string sErrorMsg = null;

            string sQuery = " SELECT ReceiptKey, ExternReceiptKey, SKU, isnull(sum(QtyExpected),0) as TotalQtyExpected" +
                            " , isnull(sum(QtyScan) +sum(QtyScanDamage) + sum(QtyScanExcess) ,0) as TotalScanQty" +
                            " , isnull(sum(QtyScan) +sum(QtyScanDamage) + sum(QtyScanExcess) ,0) - isnull(sum(QtyExpected),0) as ExcessQty" +
                            " FROM RGA_Det WHERE ReturnID='" + txtReceiptKeyNo.Text.Trim() + "'" +
                            " Group by ReceiptKey, ExternReceiptKey, SKU" +
                            " HAVING isnull(sum(QtyExpected),0) - isnull(sum(QtyScan) +sum(QtyScanDamage) + sum(QtyScanExcess) ,0) < 0 ";

            sErrorMsg = bindDataToDataGrid(sQuery, conn, ref this.dgExcess, ref this.lblExcess);

            if (!string.IsNullOrEmpty(sErrorMsg))
            {
                MessageBox.Show(sErrorMsg);
            }

            return sErrorMsg;
        }

        public string GetAlienInfo(string conn)
        {
            string sErrorMsg = null;

            string sQuery = " SELECT SKU, isnull(sum(QtyScanAlien),0) as TotalScanQty  " +
                            " FROM RGA_Det WHERE ReturnID='" + txtReceiptKeyNo.Text.Trim() + "'  and ScanCondition='ALIEN'" +
                            " Group by SKU";

            sErrorMsg = bindDataToDataGrid(sQuery, conn, ref this.dgAlien, ref this.lblAlien);

            if (!string.IsNullOrEmpty(sErrorMsg))
            {
                MessageBox.Show(sErrorMsg);
            }

            return sErrorMsg;
        }

        public string bindDataToDataGrid(string sQuery, string conn, ref DataGridView dg, ref Label lb)
        {
             string sErrorMsg = null;

            SqlConnection myConnection = new SqlConnection(conn);

            try
            {
                using (myConnection)
                {
                    SqlCommand oCmd = new SqlCommand(sQuery, myConnection);
                    //oCmd.Parameters.AddWithValue("@Fname", fName);
                    myConnection.Open();

                    clsRGAUploadHeader clsRGALoadHeader = new clsRGAUploadHeader();

                    using (SqlDataReader oReader = oCmd.ExecuteReader())
                    {
                        DataTable dt = new DataTable();
                        dt.Load(oReader);
                        dg.DataSource = dt;

                        lb.Visible = true;
                        dg.Visible = true;
                    }

                }
            }
            catch (SqlException ex)
            {
                sErrorMsg = ex.Message;
                clearPage();
            }
            catch (Exception ex)
            {
                sErrorMsg = ex.Message;
                clearPage();
            }
            finally
            {
                myConnection.Close();
            }

            return sErrorMsg;
        }

        
        //SK 20141009 - Populate data for damage
        private string PopulateDamage()
        {

            string sStorer = clsConfig.SStorer;
            string sQuery = "";
            string ReceiptKey = "";
            string ExternReceiptKey = "";
            string ExternLineNo = "";
            string CarrierKey = "";
            string WarehouseReference = "";
            string ASNType = "";
            string SKU = "";
            decimal QtyScanDamage = 0;
            string UOM = "";
            string Lottable01 = "";
            string Lottable02 = "";
            string Lottable03 = "";
            string Lottable04 = "";
            string Lottable05 = "";
            string LOC = "";



            string sQuery2 = " DELETE FROM RGA_ReceiptUpload where RETURNID ='" + txtReceiptKeyNo.Text.Trim() + "'" +
                            " AND FLAG='DAMAGE'";
            bool bResult2 = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery2);


            sQuery = "SELECT ReceiptKey, ExternLineNo, ExternReceiptKey, SKU,QtyExpected," +
                " QtyScanDamage, UOM,WarehouseReference,CarrierKey ,ASNType from RGA_Det where ReturnID ='" + txtReceiptKeyNo.Text.Trim() + "' " +
                      " AND QtyScanDamage > 0 " +
                      " ORDER BY ExternLineNo asc";
            DataSet ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);
            //need to loop thru sku

            foreach (DataTable table in ds.Tables)
            {
                if (table.Rows.Count > 0)
                {

                    foreach (DataRow dr in table.Rows)
                    {
                        ReceiptKey = Convert.ToString(dr["ReceiptKey"]).Trim().ToUpper();
                        ExternReceiptKey = Convert.ToString(dr["ExternReceiptKey"]).Trim().ToUpper();
                        ExternLineNo = Convert.ToString(dr["ExternLineNo"]).Trim().ToUpper();
                        CarrierKey = Convert.ToString(dr["CarrierKey"]).Trim().ToUpper();
                        WarehouseReference = Convert.ToString(dr["WarehouseReference"]).Trim().ToUpper();
                        ASNType = Convert.ToString(dr["ASNType"]).Trim().ToUpper();
                        SKU = Convert.ToString(dr["SKU"]).Trim().ToUpper();
                        QtyScanDamage = Convert.ToDecimal(dr["QtyScanDamage"]);
                        UOM = Convert.ToString(dr["UOM"]).Trim().ToUpper();
                        LOC = "WL-DAMAGE";




                        //insert detail part.
                        //WILEY Lottable05 Requirement: always set to 01/01/2013  00:00:00, else affect their picking process
                        Lottable05 = "01/01/2013  00:00:00";

                        sQuery = " INSERT INTO RGA_ReceiptUpload (RETURNID, STORER,RECEIPTKEY,EXTERNRECEIPTKEY,EXTERNLINENUMBER,CARRIERKEY,WAREHOUSEREFERENCE,ASNTYPE," +
                           " SKU,QTYEXPECTED,UOM, LOTTABLE01, LOTTABLE02, LOTTABLE03,LOTTABLE04,LOTTABLE05,TOLOC, DETAIL,FLAG) " +
                           " Values ('" + txtReceiptKeyNo.Text.Trim() + "','" + sStorer + "','" + ReceiptKey + "','" + ExternReceiptKey + "','" + ExternLineNo + "','" + CarrierKey + "','" + WarehouseReference + "','" + ASNType + "','" +
                           SKU + "','" + QtyScanDamage + "','" + UOM + "','" + Lottable01 + "','" + Lottable02 + "','" + Lottable03 + "','" + Lottable04 + "','" + Lottable05 + "','" + LOC + "','1','DAMAGE')";

                        bool bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);
                    }

                }
            }
            return "";

        }


        private void btnUploadRGA_Click(object sender, EventArgs e)
        {

            string sQuery = "SELECT ReceiptKey, ExternLineNo, ExternReceiptKey, SKU,QtyExpected," +
                    " QtyScanDamage, UOM,WarehouseReference,CarrierKey ,ASNType from RGA_Det where ReturnID ='" + txtReceiptKeyNo.Text.Trim() + "' " +
                          " AND QtyScanDamage > 0 " +
                          " ORDER BY ExternLineNo asc";
            DataSet ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);
            PopulateDamage();

            uploadRGA_CSV();
        }

        private void uploadRGA_CSV()
        {
            string sErrorMsg = null;

            string sQuery = "Select DETAIL,EXTERNRECEIPTKEY,EXTERNLINENUMBER,SKU, QTYEXPECTED, UOM, TOLOC, LOTTABLE05, ASNTYPE, WAREHOUSEREFERENCE, CARRIERKEY, RETURNID " + 
                            "from RGA_ReceiptUpload " + 
                            "WHERE STORER = '" + clsConfig.SStorer + "' " + 
                            "AND RETURNID = '" + txtReceiptKeyNo.Text.Trim() + "' " +
                            "AND CONVERT(bigint, QTYEXPECTED) > 0 " + 
                            "Order by EXTERNRECEIPTKEY, DETAIL, EXTERNLINENUMBER ";

            clsRGAUpload_List clsRGALoadList;

            sErrorMsg = GetRGAUploadInfo(clsConfig.SMSSQLConnectionString, sQuery, out clsRGALoadList);

            if (!string.IsNullOrEmpty(sErrorMsg))
            {
                MessageBox.Show(sErrorMsg);
                return;
            }
            else if (!clsRGALoadList.gotRecord())
            {
                MessageBox.Show("No Record!!!");
                return;
            }

            if (chkChooseDir.Checked == false)
            {
                try
                {
                    sErrorMsg = null;

                    for (int i = 0; i < clsRGALoadList.count(); i++)
                    {
                        sErrorMsg = clsRGALoadList.writeToCSV(clsRGALoadList.getHeaderByIndex(i), null, chkChooseDir.Checked);

                        if (!string.IsNullOrEmpty(sErrorMsg))
                        {
                            MessageBox.Show(sErrorMsg);
                            return;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return;
                }
            }
            else
            {
                //get file location to save to
                // Show the FolderBrowserDialog.
                DialogResult result = folderBrowserDialog1.ShowDialog();
                if (result == DialogResult.OK)
                {
                    folderName = folderBrowserDialog1.SelectedPath;

                    //clsRGALoadList.writeToCSV(txtReceiptKeyNo.Text.Trim(), folderName, chkChooseDir.Checked);
                }
            }

            MessageBox.Show("Complete");
            clearPage();

        }

        public string GetRGAUploadInfo(string conn, string sQuery, out clsRGAUpload_List clsRGALoadList)
        {
            //Select DETAIL,EXTERNRECEIPTKEY,EXTERNLINENUMBER,SKU,QTYEXPECTED, UOM, TOLOC, LOTTABLE05 from packaus.dbo.RGA_ReceiptUpload WHERE STORER = 'WILEY_AUS' AND RETURNID = '1' AND convert(bigint, QTYEXPECTED) > 0  Order by EXTERNRECEIPTKEY, DETAIL, EXTERNLINENUMBER

            string sErrorMsg = null;
            string sTempExternReceiptKey = "";
            clsRGALoadList = new  clsRGAUpload_List();

            try
            {
                using (SqlConnection myConnection = new SqlConnection(conn))
                {
                    SqlCommand oCmd = new SqlCommand(sQuery, myConnection);
                    //oCmd.Parameters.AddWithValue("@Fname", fName);
                    myConnection.Open();

                    clsRGAUploadHeader clsRGALoadHeader = new clsRGAUploadHeader();
                    int i = 0;

                    using (SqlDataReader oReader = oCmd.ExecuteReader())
                    {
                        
                        while (oReader.Read())
                        {
                            if (oReader["DETAIL"].ToString().Trim() == "0")
                            {
                                sTempExternReceiptKey = oReader["EXTERNRECEIPTKEY"].ToString().Trim();
                                if (i != 0 && sTempExternReceiptKey != clsRGALoadHeader.SReceiptKey)
                                {
                                    clsRGALoadList.addRGAHeader(clsRGALoadHeader);
                                }
                                clsRGALoadHeader = new clsRGAUploadHeader(oReader["EXTERNRECEIPTKEY"].ToString().Trim());
                                clsRGALoadHeader.SReturnID = oReader["RETURNID"].ToString().Trim();
                                clsRGALoadHeader.SASNType = oReader["ASNTYPE"].ToString().Trim();
                                i++;
                            }
                            else
                            {
                                clsRGALoadHeader.SWarehouseRef = oReader["WAREHOUSEREFERENCE"].ToString().Trim();
                                clsRGALoadHeader.SCarrierKey = oReader["CARRIERKEY"].ToString().Trim();


                                clsRGAUploadDetail clsRGALoadLine = new clsRGAUploadDetail();
                                clsRGALoadLine.SExternLineNo = oReader["EXTERNLINENUMBER"].ToString().Trim();
                                clsRGALoadLine.SSKU = oReader["SKU"].ToString().Trim();
                                clsRGALoadLine.IQtyExpected = Int64.Parse(oReader["QTYEXPECTED"].ToString().Trim());
                                clsRGALoadLine.SUOM = oReader["UOM"].ToString().Trim();
                                clsRGALoadLine.SToLoc = oReader["TOLOC"].ToString().Trim();
                                clsRGALoadLine.SLottable05 = oReader["LOTTABLE05"].ToString().Trim();
                                clsRGALoadHeader.addRGADetail(clsRGALoadLine);
                            }
                        }

                        clsRGALoadList.addRGAHeader(clsRGALoadHeader);
                    }

                }

            }
            catch (Exception ex)
            {
                
                sErrorMsg = ex.Message;
            }


            return sErrorMsg;
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Owner.Enabled = true;
            this.Close();
        }

        private void frmUploadRGA_Load(object sender, EventArgs e)
        {
            this.Owner.Enabled = false;
        }

        private void frmUploadRGA_Activated(object sender, EventArgs e)
        {
            if (this.Owner != null)
            {
                this.Owner.Enabled = false;
            }
        }

        private void frmUploadRGA_Deactivate(object sender, EventArgs e)
        {
            if (this.Owner != null)
            {
                this.Owner.Enabled = true;
            }
        }

        private bool checkIfReturnIDValid(string sReturnID)
        {
            string sQuery = "Select count(*) as counter from RGA_Det where storerkey = '" + clsConfig.SStorer + "' And ReturnID = '" + sReturnID + "'";
            DataSet ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);



            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow rd = ds.Tables[0].Rows[0];
                if (rd["counter"].ToString() == "0")
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            return false;

        }

    }
}
